using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.IO;
using System.Reflection;

namespace Christian.Helle.PocketNews.PocketPC.Data
{
    public class DatabaseEngine
    {
        private readonly string app_db_file;
        public string ConnectionString;

        #region ctor

        public DatabaseEngine()
        {
            app_db_file = string.Format("{0}\\AppDatabase.sdf",
                                        Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase));

            ConnectionString = string.Format("Data Source={0}", app_db_file);
        }

        #endregion

        #region Delete Database

        public void DeleteApplicationDatabase()
        {
            if (File.Exists(app_db_file))
            {
                var fi = new FileInfo(app_db_file);
                if (fi.Attributes != FileAttributes.Normal)
                {
                    fi.Attributes = FileAttributes.Normal;
                }
                File.Delete(app_db_file);
            }
        }

        #endregion

        #region Unlock Database

        public void UnlockApplicationDatabase()
        {
            if (File.Exists(app_db_file))
            {
                var fi = new FileInfo(app_db_file);
                if (fi.Attributes != FileAttributes.Normal)
                {
                    fi.Attributes = FileAttributes.Normal;
                }
            }
            else
            {
                throw new FileNotFoundException("AppDatabase.sdf Could not be Found");
            }
        }

        #endregion

        #region Check Database

        public void CheckApplicationDatabase()
        {
            Debug.WriteLine("Checking Database...");

            if (File.Exists(app_db_file))
            {
                Debug.WriteLine("Database exists");
                Debug.WriteLine("Checking that the database file is not write protected");

                var fi = new FileInfo(app_db_file);
                if (fi.Attributes != FileAttributes.Normal)
                {
                    fi.Attributes = FileAttributes.Normal;

                    Debug.WriteLine("Database file was write protected, changed file attributes to NORMAL");
                }
            }
            else
            {
                Debug.WriteLine("Database did not exist");

                try
                {
                    Debug.WriteLine("Creating Database");

                    using (var engine = new SqlCeEngine(ConnectionString))
                    {
                        engine.CreateDatabase();
                    }
                }
                    #region Exception Handling

                catch (Exception e)
                {
                    Debug.Assert(false,
                                 string.Format("Error creating the database due to: {0}", e.Message),
                                 e.StackTrace);

                    ErrorLog.AppendError(
                        "Error at SqlEngine::CheckApplicationDatabase()",
                        e.Message,
                        (e.InnerException != null) ? e.InnerException.Message : string.Empty,
                        e.StackTrace);
                }

                #endregion
            }

            CreateApplicationTables();
        }

        #endregion

        #region Create Tables

        #region Create Application Tables

        private void CreateApplicationTables()
        {
            Debug.WriteLine("Creating Tables");

            try
            {
                using (var conn = new SqlCeConnection(ConnectionString))
                {
                    conn.Open();

                    using (var cmd = conn.CreateCommand())
                    {
                        if (!DoesTableExist(cmd, "Newsgroup"))
                        {
                            ExecuteNonQuery(cmd,
                                            @"CREATE TABLE Newsgroup 
								(
									Id INT IDENTITY(1,1) PRIMARY KEY,
									GroupName NVARCHAR(200) NOT NULL,
									Posts INT NOT NULL,
									Low INT NOT NULL,
									High INT NOT NULL,
									Subscribed BIT
								)");

                            ExecuteNonQuery(cmd,
                                            "CREATE INDEX IDX_NewsgroupName ON Newsgroup (GroupName ASC)");
                        }

                        if (!DoesTableExist(cmd, "Draft"))
                        {
                            ExecuteNonQuery(cmd,
                                            @"CREATE TABLE Draft 
								(
									Id INT IDENTITY(1,1) PRIMARY KEY,
									UniqueId UNIQUEIDENTIFIER UNIQUE NOT NULL,
									Newsgroup NVARCHAR(200) NOT NULL,
									Subject NTEXT,
									Body NTEXT
								)");

                            ExecuteNonQuery(cmd,
                                            "CREATE INDEX IDX_Draft ON Draft (UniqueId ASC)");
                        }
                    }

                    conn.Close();
                }
            }
                #region Exception Handling

            catch (SqlCeException e)
            {
                Debug.Assert(false,
                             string.Format("Error creating the tables due to: {0}", e.Message),
                             e.StackTrace);

                ErrorLog.AppendError(
                    "Error at SqlEngine::CheckApplicationDatabase()",
                    e.Message,
                    e.StackTrace);
            }
            catch (Exception e)
            {
                Debug.Assert(false,
                             string.Format("Error creating the tables due to: {0}", e.Message),
                             e.StackTrace);

                ErrorLog.AppendError(
                    "Error at SqlEngine::CheckApplicationDatabase()",
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : string.Empty,
                    e.StackTrace);
            }

            #endregion
        }

        #endregion

        #region ExecuteNonQuery

        private void ExecuteNonQuery(SqlCeCommand command, string query)
        {
            try
            {
                command.CommandText = query; //query.Trim(new char[] { '\r', '\n' });
                var count = command.ExecuteNonQuery();

                Debug.WriteLine(
                    string.Format("ExecuteNonQuery() returned {0} for {1}",
                                  count, query));
            }
                #region Exception Handling

            catch (SqlCeException e)
            {
                Debug.Assert(false,
                             string.Format("Error executing the command: {0} due to {1}",
                                           query),
                             e.Message);

                ErrorLog.AppendError(
                    string.Format("Error executing the command: {0} at SqlEngine::ExecuteNonQuery()", query),
                    e.Message,
                    e.StackTrace);
            }
            catch (Exception e)
            {
                Debug.Assert(false,
                             string.Format("Error executing the command: {0} due to {1}",
                                           query),
                             e.Message);

                ErrorLog.AppendError(
                    string.Format("Error executing the command: {0} at SqlEngine::ExecuteNonQuery()", query),
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : string.Empty,
                    e.StackTrace);
            }

            #endregion
        }

        #endregion

        #endregion

        #region Compact Database

        public void CompactDatabase()
        {
            try
            {
                Cache.CloseConnections();

                using (var engine = new SqlCeEngine(ConnectionString))
                {
                    engine.Compact(null);
                }
            }
            catch (Exception e)
            {
                Debug.Assert(false, e.Message, e.StackTrace);

                ErrorLog.AppendError(
                    "Unable to compress database.",
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : null,
                    e.StackTrace);
            }
        }

        #endregion

        #region Does Table Exist?

        public static bool DoesTableExist(string tablename)
        {
            using (var cmd = (SqlCeCommand) Cache.Connection.CreateCommand())
            {
                return DoesTableExist(cmd, tablename);
            }
        }

        public static bool DoesTableExist(SqlCeCommand cmd, string tablename)
        {
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                    @"SELECT COUNT(TABLE_NAME) 
					  FROM INFORMATION_SCHEMA.TABLES 
					  WHERE TABLE_NAME=@Name";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Name", tablename);

                var obj = cmd.ExecuteScalar();
                return Convert.ToBoolean(obj);
            }
            catch (SqlCeException e)
            {
                Debug.Assert(false, e.Message, e.StackTrace);

                ErrorLog.AppendError(
                    "SqlCeException - Unable to check if table exists in database.",
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : null,
                    e.StackTrace);
            }
            catch (NullReferenceException e)
            {
                Debug.Assert(false, e.Message, e.StackTrace);

                ErrorLog.AppendError(
                    "NullReferenceException - Unable to check if table exists in database.",
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : null,
                    e.StackTrace);
            }
            catch (Exception e)
            {
                Debug.Assert(false, e.Message, e.StackTrace);

                ErrorLog.AppendError(
                    "Exception - Unable to check if table exists in database.",
                    e.Message,
                    (e.InnerException != null) ? e.InnerException.Message : null,
                    e.StackTrace);
            }

            return false;
        }

        #endregion
    }
}